const express = require("express");
const router = express.Router();

const dayjs = require("dayjs");
const { Op, QueryTypes } = require("sequelize");

const {
  Comment,
  Dish,
  DailyMenu,
  DailyMenuDish,
  sequelize,
  UserComment,
  User,
} = require("../models");
const { NotFoundError, success, failure } = require("../utils/response");

/**
 * 分页查询已评论
 * userId
 * dishId
 */
router.post("/mine", async (req, res) => {
  try {
    const { userId, dishId } = req.body;
    const pageNumber = Math.abs(Number(req.body.pageNumber)) || 1;
    const pageSize = Math.abs(Number(req.body.pageSize)) || 10;
    const offset = (pageNumber - 1) * pageSize;
    const sql = `SELECT * FROM Comments WHERE userId = ${userId} AND dishId = ${dishId} LIMIT ${offset}, ${pageSize}`;
    const countSql = `SELECT COUNT(*) AS total FROM Comments WHERE userId = ${userId} AND dishId = ${dishId} LIMIT ${offset}, ${pageSize}`;

    const [[{ total }]] = await sequelize.query(countSql);
    // return res.json({ total });

    let [comments] = await sequelize.query(sql);
    comments = comments.map((e) => {
      e.score = parseFloat(parseFloat(e.score).toFixed(1));
      e.createdAt = dayjs(e.createdAt).format("YYYY-MM-DD HH:mm:ss");
      return e;
    });
    success(res, "", {
      comments,
      pagination: {
        total,
        pageNumber,
        pageSize,
      },
    });
  } catch (error) {}
});

/**
 * 根据菜品 id 分页获取评论，
 * dishId
 * userId？
 * pageSize
 * pageNumber
 */
router.post("/getByDishId", async (req, res) => {
  try {
    // 获取查询参数 title
    const query = req.body;

    // 分页的3个参数 pageNumber pageSize offset
    const pageNumber = Math.abs(Number(query.pageNumber)) || 1;
    const pageSize = Math.abs(Number(query.pageSize)) || 10;
    const offset = (pageNumber - 1) * pageSize;

    // return res.json({ query });

    const sql = `
SELECT 
    c.id,
    c.userId,
    c.dishId,
    c.date,
    c.score,
    c.comment,
    c.likeCount,
    c.createdAt,
    CASE 
        WHEN uc.id IS NOT NULL THEN TRUE 
        ELSE FALSE 
    END AS liked
FROM 
    Comments c
LEFT JOIN 
    UserComments uc 
ON 
    c.id = uc.commentId AND uc.userId = ${query.userId}
WHERE 
    c.dishId = ${query.dishId}
ORDER BY 
    c.createdAt DESC
LIMIT 
    ${offset}, ${pageSize};
  `;
    // const [comments] = await sequelize.query(sql);

    const countSql = `SELECT COUNT(*)  AS total FROM Comments WHERE dishId = ${query.dishId}`;

    let [[comments], [[{ total }]]] = await Promise.all([
      sequelize.query(sql),
      sequelize.query(countSql),
    ]);

    comments = comments.map((e) => {
      e.createdAt = dayjs(e.createdAt).format("YYYY-MM-DD HH:mm:ss");
      e.liked = Boolean(e.liked);
      return e;
    });
    // res.json({ comments, pagination: { total, pageSize, pageNumber } });
    success(res, "success", {
      comments,
      pagination: { total, pageSize, pageNumber },
    });
  } catch (error) {}
});

/**
 * 能否评论
 * userId
 * dishId
 * 只有今日菜单中的菜品可以评价
 * 根据userId dishId 查询
 */
router.post("/mayComment", async (req, res) => {
  const { userId, dishId } = req.body;
  if (!userId || !dishId) return success(res, "参数错误");
  // 1. 菜品是否在今日菜单中，否，返回false
  // 1.1 根据date(当天日期)查询DailyMenus表，获取dailyMenuId，否，返回false。
  // res.json({ date, userId, dishId });
  // 1.2 根据dailyMenuId查询DailyMenuDishes表，获取所有菜品id

  // 1.3 判断dishId是否在今日菜品id中，否，返回false。
  // 2. 根据userId dishId Date 查询评论表，有记录则返回false，无则返回true

  const date = dayjs().format("YYYY-MM-DD");
  // return res.json({ date });

  // 根据userId,dishId,date查询评论记录
  const commentsCountSql = `SELECT * FROM Comments WHERE userId = ${userId} AND dishId = ${dishId} AND \`date\` = '${date}'`;

  // 根据userId,dishId,date查询是否可以评论
  const canCommentSql = `
  -- 1可以评论，0不可以评论
  SELECT 
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM Comments c 
            WHERE c.userId = ${userId} AND c.dishId = ${dishId} AND DATE(c.date) = '${date}'
        ) THEN 0 -- 用户已经评论过了
        WHEN EXISTS (
            SELECT 1 
            FROM DailyMenuDishes dmd 
            JOIN DailyMenus dm ON dmd.dailyMenuId = dm.id 
            WHERE dmd.dishId = ${dishId} AND DATE(dm.date) = '${date}'
        ) THEN 1 -- 菜品在今天的菜单中
        ELSE 0 -- 菜品不在今天的菜单中
				END AS can_comment;
  `;

  const [[[res1]], [res2]] = await Promise.all([
    sequelize.query(canCommentSql),
    sequelize.query(commentsCountSql),
  ]);

  // return res.json({
  //   canComment: res1.can_comment === 0 ? false : true,
  //   commentCount: res2.length,
  // });

  return success(res, "", {
    canComment: res1.can_comment === 0 ? false : true,
    commentCount: res2.length,
  });
});

/**
 * 创建评论
 * POST /createDish
 */
router.post("/createComment", async (req, res) => {
  try {
    // 白名单过滤
    const body = {
      userId: req.body.userId,
      dishId: req.body.dishId,
      // date: req.body.date,
      score: req.body.score,
      comment: req.body.comment,
    };
    // console.log("body", body);
    const date = dayjs().format("YYYY-MM-DD");
    body.date = date;
    // 1.查询评论是否可以评论
    const canCommentSql = `
  -- 2菜品不在今日菜单中，1可以评论，0不可以评论
  SELECT 
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM Comments c 
            WHERE c.userId = ${body.userId} AND c.dishId = ${body.dishId} AND DATE(c.date) = '${date}'
        ) THEN 0 -- 用户已经评论过了
        WHEN EXISTS (
            SELECT 1 
            FROM DailyMenuDishes dmd 
            JOIN DailyMenus dm ON dmd.dailyMenuId = dm.id 
            WHERE dmd.dishId = ${body.dishId} AND DATE(dm.date) = '${date}'
        ) THEN 1 -- 菜品在今天的菜单中
        ELSE 2 -- 菜品不在今天的菜单中
				END AS can_comment;
  `;

    const [[{ can_comment }]] = await sequelize.query(canCommentSql);
    if (can_comment !== 1) {
      return success(res, "不可评论");
    }

    // 创建评论
    const comment = await Comment.create(body);
    // 修改菜品的评分和评分次数
    const dish = await Dish.findByPk(body.dishId);
    if (!dish) return success(res, "菜品不存在");
    let oldScore = dish.score;
    let oldscoreCount = dish.scoreCount;
    const newScore =
      (oldScore * oldscoreCount + body.score) / (oldscoreCount + 1);
    await dish.update({
      score: newScore,
      scoreCount: oldscoreCount + 1,
    });
    // 返回数据：状态码201
    success(res, "创建评论成功", { comment }, 201);
  } catch (error) {
    failure(res, error);
  }
});

/**
 * 修改评论
 * id
 * score
 * comment
 */
router.post("/update", async (req, res) => {
  try {
    // 评论是否存在

    // const { id } = req.body;
    const id = req.body.commentId;
    const body = {
      score: req.body.score,
      comment: req.body.comment,
    };
    console.log("id", id);
    console.log("body", body);

    let comment = await Comment.findByPk(id);
    if (!comment) {
      comment = await comment.create(body);
    }
    // 修改前的评分
    const beforeScore = comment.score;
    const dishId = comment.dishId;

    console.log("comment", comment);
    // 修改菜品的评分和评分次数
    const dish = await Dish.findByPk(dishId);
    if (dish) {
      let oldScore = dish.score;
      let oldscoreCount = dish.scoreCount;
      const newScore =
        (oldScore * oldscoreCount - beforeScore + body.score) / oldscoreCount;
      await dish.update({
        score: newScore,
        scoreCount: oldscoreCount,
      });
    }

    await comment.update(body);

    success(res, "修改成功");
  } catch (error) {
    res.status(500).json({
      status: false,
      message: "服务器错误",
      errors: [error.message],
    });
  }
});

/**
 * 根据id删除一个评论
 */
router.post("/deleteById", async (req, res) => {
  try {
    // 根据id查询
    // const { id } = req.body;
    const id = req.body.commentId;
    const comment = await Comment.findByPk(id);

    // 修改菜品的评分
    if (comment) {
      const userScore = comment.score;
      const dishId = comment.dishId;

      const dish = await Dish.findByPk(dishId);
      if (dish) {
        const oldScore = dish.score;
        const oldscoreCount = dish.scoreCount;
        const newScore =
          (oldScore * oldscoreCount - userScore) / (oldscoreCount - 1);
        await dish.update({
          score: newScore,
          scoreCount: oldscoreCount - 1,
        });
      }
    }

    comment?.destroy();

    success(res, "删除评论成功");
  } catch (error) {
    failure(res, error);
  }
});

/**
 * 点赞评论
 * suerId
 * commentId
 */
router.post("/liked", async (req, res) => {
  try {
    const userId = parseInt(req.body.userId);
    const commentId = parseInt(req.body.commentId);
    // return res.json({ userId, commentId });
    // 1.评论存在
    const comment = await Comment.findByPk(commentId);
    // 2.评论不存在
    if (!comment) {
      return success(res, "评论不存在");
    }
    // 3.不能给自己点赞
    if (comment.userId === userId) {
      return success(res, "不能给自己点赞");
    }

    // 根据userId, commentId查询UserComments表，获取点赞记录;
    const likedExistSql = `SELECT * FROM UserComments WHERE userId = ${userId} and commentId = ${commentId}`;
    const [likedRecords] = await sequelize.query(likedExistSql);

    // 5. 未点赞，则创建点赞
    if (likedRecords.length === 0) {
      await UserComment.create({
        userId,
        commentId,
      });
      // 5.1 评论点赞次数+1
      comment.update({
        likeCount: comment.likeCount + 1,
      });

      return success(res, "点赞成功");
    } else {
      // 4. 如果已点赞（有点赞记录）则取消点赞,
      const deleteLikedSql = `DELETE FROM UserComments WHERE userId = ${userId} and commentId = ${commentId}`;
      await sequelize.query(deleteLikedSql);

      comment.update({
        likeCount: comment.likeCount > 1 ? comment.likeCount - 1 : 0,
      });
      return success(res, "取消点赞成功");
    }
  } catch (error) {
    failure(res, error);
  }
});
module.exports = router;
